package com.elite.groovy.callback

import com.elite.groovy.kh.KHDBConfig
import com.elite.groovy.kh.KHExcelUtil
import com.elite.groovy.kh.KHFileUtil
import com.elite.groovy.kh.KHUtil
import com.elite.groovy.kh.LogToDB
import com.elite.groovy.util.EliteBean
import com.elite.groovy.util.FileUploadUtil
import com.elite.groovy.util.Util
import com.wisdge.dataservice.Result
import net.sf.json.JSONArray
import net.sf.json.JSONObject
import org.apache.commons.lang.StringEscapeUtils
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.springframework.jdbc.core.JdbcTemplate

import javax.servlet.http.HttpServletRequest
import javax.servlet.http.HttpServletResponse
import java.text.SimpleDateFormat
import java.util.regex.Matcher
import java.util.regex.Pattern

/**
 * @projectName: ngsapi_cs
 * @package: cn.com.callback
 * @className: apiDataExport
 * @author: LiJT
 * @description: 数据导出服务
 * @date: 2022-12-19 14:03
 * @version: 1.0
 */

public class apiDataExport implements ServiceBackInvoke {
    private static final Log log = LogFactory.getLog(apiDataExport.class);
    private static String out_DomainURL = "";
    private static String in_DomainURL = "";
    private static String TempFilesPath = "";
    private static String tmp_FileDir = "";
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");

    public String doCallBack(EliteBean eliteBean, HttpServletRequest request, HttpServletResponse response) {

        long startTime = System.currentTimeMillis();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");

        String callBody = ""; // 入参请求体
        JSONObject resJson = new JSONObject();
        String export_type = ""; // 导出类型

        try {

            // 获取系统对外域名
            out_DomainURL = KHUtil.isNull(out_DomainURL) ? KHDBConfig.getAPIURL(eliteBean, "SYS-OuterDomain") : out_DomainURL;
            out_DomainURL = out_DomainURL.endsWith("/") ? out_DomainURL : out_DomainURL + "/";

            in_DomainURL = KHUtil.isNull(in_DomainURL) ? KHDBConfig.getAPIURL(eliteBean, "SYS-InnerDomain") : in_DomainURL;
            in_DomainURL = in_DomainURL.endsWith("/") ? in_DomainURL : in_DomainURL + "/";

            TempFilesPath = KHUtil.isNull(TempFilesPath) ? KHDBConfig.getParamConfig(eliteBean, "SYS_TempFilesPath") : TempFilesPath;
            TempFilesPath = TempFilesPath.endsWith("/") ? TempFilesPath : TempFilesPath + "/";

            // 调用类型校验,此接口仅支持GET
            String getMethod = request.getMethod();
            log.debug("getMethod:" + getMethod);
            if (!getMethod.equals("POST")) {
                log.error("HTTP请求调用类型错误，接口调用失败：" + getMethod);
                return errorRedirect(response, "调用类型异常", "HTTP请求调用类型错误，接口调用失败：" + getMethod);
            }

            // 获取所有Header
            Map<String, String> mapHeader = new HashMap<String, String>();
            Enumeration headerNames = request.getHeaderNames();
            while (headerNames.hasMoreElements()) {
                String key = (String) headerNames.nextElement();
                String value = request.getHeader(key);
                log.debug("Call Header-" + key + ":" + value);

                mapHeader.put(key, value);
            }

            // 获取所有参数名
            Map<String, String> mapParams = new HashMap<String, String>();
            Enumeration ep = request.getParameterNames();
            while (ep.hasMoreElements()) {
                String key = (String) ep.nextElement();
                String value = request.getParameter(key);
                log.debug("Call Parameter-" + key + ":" + value);
                mapParams.put(key, value);

            }

            // 获取调用数据
            callBody = Util.getRequestBody(request, "UTF-8");
            log.info("接收调用 POST 参数信息：" + callBody);

            // 获取调用报文
            JSONObject callJson = JSONObject.fromObject(callBody);

            // 获取导出类型
            export_type = KHUtil.GetSFJV(callJson, "export_type"); // 导出类型

            if (export_type == "WO") {
                resJson = doWOExport(eliteBean, response, callJson);

            } else if (export_type == "CHATHIS") {
                resJson = doCHATHisExport(eliteBean, response, callJson);

            } else {
                resJson = errorRedirect(response, "操作类型异常", "不支持的操作类型export_type:" + export_type);

            }
        } catch (Exception e) {
            log.error(Util.getERRO(e));
            resJson = errorRedirect(response, "全局处理异常", "全局处理异常:" + e.getMessage());
        } finally {
            LogToDB.toDB(eliteBean, "dataSource", "数据导出服务[Excel]", "apiDataExport", callBody, resJson.toString(), "本次发送总耗时：" + (System.currentTimeMillis() - startTime).toString(), export_type);

        }

        return resJson;

    }

    // 工单数据导出
    public static JSONObject doWOExport(EliteBean eliteBean, HttpServletResponse response, JSONObject callJson) {

        tmp_FileDir = "工单数据_" + sdf.format(new Date());

        // 获取数据源List<Map<String, Object>>
        JdbcTemplate jt = new JdbcTemplate(eliteBean.getDataSource());
        log.debug("初次嵌入请求 返回Html页面参数信息【callJson】：" + callJson);

        JSONArray exportArr = callJson.getJSONArray("data");
        String fieldsInfo = KHUtil.GetSFJV(callJson, "fields");
        String[] fieldsArr = fieldsInfo.split(";");
        List<String> headFields = new ArrayList<>();

        List<LinkedHashMap<String, Object>> mapArrayList = new ArrayList<>();

        // Init工单数据明细SQL
        String exportListSQL = '''Select R1.Workorderid,
                                   R1.Createddate,
                                   So.Objectivestatusdescription,
                                   R3.Workordertype2desc,
                                   Cu.Customername,
                                   Cu.Mobile,
                                   Cc1.Code_Name,
                                   Ccp.Province_Desc,
                                   Cct.City_Desc,
                                   Cca.Area_Desc,
                                   R1.Fakes_Address_Details,
                                   R1.Fakes_Store,
                                   R1.Fakes_Account,
                                   R1.Fakes_Link,
                                   R1.Remark
                              From Tabcworkorder R1
                              Left Join Objective Obj
                                On R1.Relation_Id = Obj.Objective_Guid
                              Left Join Sys_Workordertype2 R3
                                On R1.Workordertype2 = R3.Workordertype2_Id
                              Left Join Customer Cu
                                On R1.Customer_Guid = Cu.Customer_Guid
                              Left Join Sysc_Objectivestatus So
                                On Obj.Objectivestatus = So.Objectivestatus
                              Left Join c_Codetables Cc1
                                On Cc1.Use_Type = 'salechannel'
                               And Cc1.Use_Status = '1'
                               And Cc1.Code_Id = R1.Salechannel_Id
                              Left Join c_Province Ccp
                                On R1.Province = Ccp.Province_Id
                              Left Join c_City Cct
                                On R1.City = Cct.City_Id
                              Left Join c_Area Cca
                                On R1.Area = Cca.Area_Id
                             Where R1.Workorderid = ?''';

        // Init工单是否有附件SQL
        String attachListSQL = '''Select Up.Uploadguid,
                                           Up.Path,
                                           Up.Filename,
                                           Up.Filedesc,
                                           R1.Upload_Class1_Desc,
                                           R2.Upload_Class2_Desc
                                      From Uploadlist Up
                                      Left Join Sysc_Upload_Class1 R1
                                        On Up.Upload_Class1_Id = R1.Upload_Class1_Id
                                      Left Join Sysc_Upload_Class2 R2
                                        On R1.Upload_Class1_Id = R2.Upload_Class1_Id
                                       And Up.Upload_Class2_Id = R2.Upload_Class2_Id
                                     Where Up.Guid = ?
                                     Order By Up.Upload_Class2_Id''';

        for (int i = 0; i < exportArr.size(); i++) {
            String Workorderid = exportArr.getString(i);

            // 查询工单明细数据
            List<Map<String, Object>> woDataList = jt.queryForList(exportListSQL, Workorderid);

            // 记录工单明细数据 Iterator迭代器
            Iterator<Map<String, Object>> it = woDataList.iterator();
            while (it.hasNext()) {
                Map<String, Object> dataMap = it.next();

                LinkedHashMap<String, Object> map = new LinkedHashMap<>();
                for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
                    System.out.println(entry.getKey() + ":" + entry.getValue());
                    map.put(entry.getKey(), entry.getValue());

                    // 如果是第一条数据则将列头
                    if (i == 0) {
                        headFields.add(entry.getKey());
                    }
                }
                mapArrayList.add(map);
            }

            // 查询工单附件数据
            List<Map<String, Object>> attachList = jt.queryForList(attachListSQL, Workorderid);

            int attach_Seq = 0;
            String old_Class_Desc = "";
            for (int j = 0; j < attachList.size(); j++) {
                Map<String, Object> attachData = attachList.get(j);
                String Uploadguid = attachData.get("Uploadguid");
                String Path = attachData.get("Path");
                String Filename = attachData.get("Filename");
                String Upload_Class1_Desc = attachData.get("Upload_Class1_Desc");
                String Upload_Class2_Desc = attachData.get("Upload_Class2_Desc");

                // 文件序号处理
                if (old_Class_Desc != Upload_Class1_Desc + "_" + Upload_Class2_Desc) {
                    attach_Seq = 0;
                    old_Class_Desc = Upload_Class1_Desc + "_" + Upload_Class2_Desc
                }
                attach_Seq = attach_Seq + 1;

                // 生成文件名
                String save_FileName = Upload_Class1_Desc + "_" + Upload_Class2_Desc + "[" + attach_Seq + "]" + KHUtil.getIntRandom(6) + "-" + Filename;
                String save_FileDir = TempFilesPath + tmp_FileDir + "/附件(" + (i + 1) + ")-" + Workorderid + "_v" + tmp_FileDir.replace("工单数据_", "") + "/";
                String save_FilePath = save_FileDir + save_FileName;

                // 临时目录检查&创建
                Util.createDir(save_FileDir);

                // 下载文件
                boolean downFlag = false;
                try {
                    downFlag = Util.httpDownLoadFile(in_DomainURL + "ngs/fs/get?file=" + Path, save_FilePath, 12000);
                    log.info("附件-" + j + "文件下载成功！...");

                } catch (Exception e) {
                    log.error("附件-" + j + "文件下载到服务器-处理异常");
                }

            }

        }

        // 导出Excel列头生成
        List<String> head = new ArrayList<>();
        if (KHUtil.isNull(fieldsInfo)) {
            head = headFields;
        } else {
            Arrays.stream(fieldsArr).forEach(element -> {
                // 处理每个元素
                head.add(element);
            });
        }
        log.debug("导出Excel列头生成:" + head);

        try {
            // 生成Excel文件
            String exportPath = KHExcelUtil.getInstance().exportExcel(mapArrayList, head, TempFilesPath + tmp_FileDir + "/", "工单数据清单-v" + tmp_FileDir.replace("工单数据_", ""));
            log.debug("导出Excel存储路径:" + exportPath);

            // 压缩文件夹Zip
            String zipFilePath = TempFilesPath + tmp_FileDir + "/" + tmp_FileDir + ".zip";
            String doLinuxCmd = "cd " + TempFilesPath + tmp_FileDir + System.lineSeparator() + "rm -rf " + zipFilePath + System.lineSeparator() + "zip -r " + tmp_FileDir + ".zip *";
            log.debug("压缩文件执行命令:" + doLinuxCmd);

            KHFileUtil.doLinuxCmd(doLinuxCmd);
            log.debug("压缩文件存储路径:" + zipFilePath);

            // 上传压缩包
            File tempFile = new File(zipFilePath.trim());
            String uploadResult = FileUploadUtil.uploadFile(in_DomainURL + "ngs/", KHDBConfig.getParamConfig(eliteBean, "NGS_fileServerKey"), "/export", tempFile);
            log.debug("文件上传NGS结果：" + uploadResult);

            JSONObject resultJson = JSONObject.fromObject(uploadResult);

            String code = KHUtil.GetSFJV(resultJson, "code");
            if (code.equals("1")) {
                return JSONObject.fromObject(new Result(Result.SUCCESS, resultJson.getJSONArray("value").getString(0), out_DomainURL + "ngs/fs/get?file=" + resultJson.getJSONArray("value").getString(0)));
            } else {
                return errorRedirect(response, "文件上传NGS异常", "文件上传NGS结果异常:" + KHUtil.GetSFJV(resultJson, "message"));
            }

        } catch (Exception e) {
            log.error("Excel文件处理异常", e);
            return errorRedirect(response, "Excel文件处理异常", "Excel文件处理异常:" + e.getMessage());
        } finally {
            boolean del_flag = KHFileUtil.deleteDiretory(TempFilesPath + tmp_FileDir);
            log.error("End临时文件删除结果:" + del_flag);

        }

    }

    // 网聊历史数据导出
    public static JSONObject doCHATHisExport(EliteBean eliteBean, HttpServletResponse response, JSONObject callJson) {

        log.debug("网聊历史数据_参数信息【callJson】：" + callJson);


        JSONArray headArr = callJson.getJSONArray("fields");
        JSONArray dataArr = callJson.getJSONArray("data");

        int headNum = headArr.size();
        int dataNum = dataArr.size();

        // 表头
        Map<String, String> headMap = new HashMap<String, String>();
        List<String> headList = new ArrayList<>();
        for (int i = 0; i < headNum; i++) {
            // 表头
            String colName = KHUtil.GetSFJV(headArr.getJSONObject(i), "colName");
            String display = KHUtil.GetSFJV(headArr.getJSONObject(i), "display");
            String width = KHUtil.GetSFJV(headArr.getJSONObject(i), "width");
            String realCol = KHUtil.GetSFJV(headArr.getJSONObject(i), "realCol");
            String editable = KHUtil.GetSFJV(headArr.getJSONObject(i), "editable");
            log.debug("表头字段过滤结果 colName:" + colName + " display:" + display + " width:" + width + " realCol:" + realCol + " editable:" + editable);

            // 表头字段过滤
            if (KHUtil.isNull(colName) && !KHUtil.isNull(display) && !Objects.equals(width, "0") && (Objects.equals(editable, "0") || KHUtil.isNull(editable))) {
                headMap.put(display, realCol);
                headList.add(display);
            }
        }
        int fieldsNum = headList.size();
        log.debug("表头字段过滤结果:" + headMap);

        headList.add("聊天记录(明细)")
        log.debug("导出Excel列头生成:" + headList);

        // 数据列
        List<LinkedHashMap<String, Object>> dataList = new ArrayList<>();

        for (int i = 0; i < dataNum; i++) {
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            JSONArray dataRow = dataArr.getJSONArray(i);

            for (int j = 0; j <= fieldsNum; j++) {
                if (j < fieldsNum) {
                    String headField = headList.get(j);
                    String headCol = headMap.get(headField);

                    map.put("Col_" + (j + 1), dataRow.getString(Integer.parseInt(headCol)));
                } else {

                    // 获取聊天历史
                    map.put("Col_" + (j + 1), getChat_Msg(eliteBean, dataRow.getString(0)));
                }
            }
            dataList.add(map);
        }

        try {
            // 生成Excel文件
            String exportPath = KHExcelUtil.getInstance().exportExcel(dataList, headList, TempFilesPath + tmp_FileDir + "/", "网聊历史数据清单-v" + tmp_FileDir.replace("网聊历史数据_", ""));
            log.debug("导出Excel存储路径:" + exportPath);

            // 上传压缩包
            File tempFile = new File(exportPath.trim());
            String uploadResult = FileUploadUtil.uploadFile(in_DomainURL + "ngs/", KHDBConfig.getParamConfig(eliteBean, "NGS_fileServerKey"), "/export", tempFile);
            log.debug("文件上传NGS结果：" + uploadResult);

            JSONObject resultJson = JSONObject.fromObject(uploadResult);

            String code = KHUtil.GetSFJV(resultJson, "code");
            if (code.equals("1")) {
                return JSONObject.fromObject(new Result(Result.SUCCESS, resultJson.getJSONArray("value").getString(0), out_DomainURL + "ngs/fs/get?file=" + resultJson.getJSONArray("value").getString(0)));
            } else {
                return errorRedirect(response, "文件上传NGS异常", "文件上传NGS结果异常:" + KHUtil.GetSFJV(resultJson, "message"));
            }

        } catch (Exception e) {
            log.error("Excel文件处理异常", e);
            return errorRedirect(response, "Excel文件处理异常", "Excel文件处理异常:" + e.getMessage());
        } finally {
            boolean del_flag = KHFileUtil.deleteDiretory(TempFilesPath + tmp_FileDir);
            log.error("End临时文件删除结果:" + del_flag);

        }

    }

    // 异常&错误重定向
    public static JSONObject errorRedirect(HttpServletResponse response, String ErrorTitle, String ErrorInfo) {
        log.warn("异常&错误[apiDynLogin]重定向 ErrorTitle:" + ErrorTitle + " ErrorInfo:" + ErrorInfo);

        return JSONObject.fromObject(new Result(Result.ERROR, ErrorTitle, ErrorInfo));
    }

    // 工具方法
    public static String getQueryString(Map<String, String> params) {
        String url = "";
        if (params == null) {
            return url;
        }

        StringBuilder builder = new StringBuilder(url);

        int i = 0;
        for (String key : params.keySet()) {
            String value = params.get(key);
            if (value == null) { // 过滤空的key
                continue;
            }

            if (i != 0) {
                builder.append('&');
            }

            builder.append(key);
            builder.append('=');
            builder.append(encode(value));

            i++;
        }

        return builder.toString();
    }

    // 工具方法
    public static String encode(String input) {
        if (input == null) {
            return "";
        }

        try {
            return URLEncoder.encode(input, "utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        return input;
    }

    // 获取拼接某个会话聊天历史
    public static String getChat_Msg(EliteBean eliteBean, String session_id) {
        String SQL = '''Select Case
                                 When Length(R1.Chat_User_Id) > 6 Then
                                  '客户:'
                                 When R3.Id Is Not Null Then
                                  '机器人:'
                                 When R2.Staff_Id Is Not Null Then
                                  '客服:'
                                 Else
                                  Concat(R2.Staffname, ':')
                               End As Chat_User_Id,
                               R1.Post_Time,
                               R1.Message_Text,
                               R1.Chat_User_Id
                          From Chat_Message R1
                          Left Join Staff R2
                            On R1.Chat_User_Id = R2.Staff_Id
                          Left Join Robot R3
                            On R1.Chat_User_Id = R3.Bot_Name
                         Where R1.Chat_Session_Id = \'''' + session_id + '''\'
                         Order By 2''';

        List SQLRes = eliteBean.chaxun_sql(SQL, "dataSource");

        StringBuffer to_Msg_Info = new StringBuffer();
        for (int i = 0; i < SQLRes.size(); i++) {
            String Chat_User_Id = SQLRes.get(i).getAt("Chat_User_Id");
            String Post_Time = SQLRes.get(i).getAt("Post_Time");
            String Message_Text = SQLRes.get(i).getAt("Message_Text");

            Message_Text = dealMsg_Text(Message_Text);

            if (KHUtil.isNull(to_Msg_Info.toString())) {
                to_Msg_Info.append(Chat_User_Id + Message_Text);
            } else {
                to_Msg_Info.append(System.lineSeparator() + System.lineSeparator() + Chat_User_Id + Message_Text);

            }

        }

        return to_Msg_Info;
    }

    public static String dealMsg_Text(String Message_Text) {
        Message_Text = StringEscapeUtils.unescapeHtml(Message_Text);
        try {
            if (Message_Text.startsWith("{")) {
                JSONObject MessageBody = JSONObject.fromObject(Message_Text);
                Message_Text = MessageBody.getJSONObject("data").getString("answer");
            } else if(Message_Text.startsWith("<img src=")){
                Message_Text=Message_Text.replace("<img src=\"","").replace("\">","");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return StripHTML(Message_Text);

    }

    public static String StripHTML(String str) {
        //如果有双引号将其先转成单引号
        String htmlStr = str.replaceAll("\"", "'");
        String regEx_script = "<script[^>]*?>[\\s\\S]*?<\\/script>"; // 定义script的正则表达式
        String regEx_style = "<style[^>]*?>[\\s\\S]*?<\\/style>"; // 定义style的正则表达式
        String regEx_html = "<[^>]+>"; // 定义HTML标签的正则表达式

        Pattern p_script = Pattern.compile(regEx_script, Pattern.CASE_INSENSITIVE);
        Matcher m_script = p_script.matcher(htmlStr);
        htmlStr = m_script.replaceAll(""); // 过滤script标签

        Pattern p_style = Pattern.compile(regEx_style, Pattern.CASE_INSENSITIVE);
        Matcher m_style = p_style.matcher(htmlStr);
        htmlStr = m_style.replaceAll(""); // 过滤style标签

        Pattern p_html = Pattern.compile(regEx_html, Pattern.CASE_INSENSITIVE);
        Matcher m_html = p_html.matcher(htmlStr);
        htmlStr = m_html.replaceAll(""); // 过滤html标签

        return htmlStr.trim(); //返回文本字符串
    }

}